/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fresher.model;

import java.sql.ResultSet;
import java.sql.SQLException;

import fresher.utility.DatabaseUtility;
import fresher.objects.BillDetail;
import java.util.*;

/**
 *
 * @author nguyenbt
 */
public class BillDetailModel {
    /**
     * Get all details of bill
     *
     * @return all details
     */
    public ArrayList<BillDetail> getDetailsOfBill(String bill_id){
        ArrayList<BillDetail> results = new ArrayList<BillDetail>();
        DatabaseUtility database = new DatabaseUtility();
        String sql = "SELECT * FROM bill_detail where bill_id="+bill_id;
        try {
            ResultSet rs = database.query(sql);
            while (rs.next()){
                BillDetail detail = new BillDetail();
                detail.setId(rs.getInt("id"));
                detail.setBillID(rs.getInt("bill_id"));
                detail.setFood(rs.getString("food"));
                detail.setPrice(rs.getInt("price"));
                results.add(detail);
            }    
        } catch (SQLException sqlException) {
            //process exception
            database.close();
        }
        database.close();
        return results;
    }
    
    /**
     * Get Detail with ID
     *
     * @return detail (if any) or null
     */
    public BillDetail getDetailByID(String id) {
        DatabaseUtility database = new DatabaseUtility();
        String sql = "SELECT * FROM bill_detail WHERE id = " + id;
        BillDetail detail = null;
        try {
            ResultSet rs = database.query(sql);
            if (rs.next()) {
                detail = new BillDetail();
                detail.setId(rs.getInt("id"));
                detail.setBillID(rs.getInt("bill_id"));
                detail.setFood(rs.getString("food"));
                detail.setPrice(rs.getInt("price"));
            }
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
        return detail;
    }
    
    /**
     * Add detail of bill
     *
     * @return is added?
     */
    public boolean addDetailsToBill(String billID, String food, String price) {
        boolean result = false;
        
        DatabaseUtility database = new DatabaseUtility();
        String sql = "INSERT INTO bill_detail(`bill_id`,`food`,`price`)"
                + "VALUES (" + billID + ", '" + food + "', " + price + ")";
        System.out.println(sql);
        try {
            if(database.executeUpdate(sql)!=0)
                result = true;
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
        return result;
    }
    
    /**
     * Update detail of bill
     *
     * @return is added?
     */
    public boolean updateBillDetail(String id, String food, String price) {
        boolean result = false;
        
        DatabaseUtility database = new DatabaseUtility();
        String sql = "UPDATE bill_detail SET food= '" + food + "', price = " + price + " WHERE "
                + "id = " + id;
        System.out.println(sql);
        try {
            if(database.executeUpdate(sql)!=0){
                result = true;
            }
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
        return result;
    }
    
    /**
     * Check total of bill
     *
     * @return total of bill
     */
    public int getTotalOfBill(int billID)
    {
        int total = 0;
        DatabaseUtility database = new DatabaseUtility();
        String sql = "SELECT SUM(price) as total FROM bill_detail WHERE bill_id="+billID;
        try {
            ResultSet rs = database.query(sql);
            if(rs.next()){
                total = rs.getInt("total");
            }
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
        return total;
    }
    
    /**
     * Delete detail of bill
     *
     * @return is deleted?
     */
    public boolean deleteBillDetail(String id){
        boolean result = false;
        DatabaseUtility database = new DatabaseUtility();
        String sql = "DELETE FROM bill_detail WHERE id=" + id;
        try {
            if(database.executeUpdate(sql)!=0)
                result = true;
        } catch (SQLException sqlException) {
            database.close();
        }
        database.close();
        return result;
    }
}
